package com.oceansoft.core.common.util;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * @author lawal 
 *  2011-9-21
 * 
 */
public class List2Excel{
	static final ThreadLocal<List2Excel> threadLocal = new ThreadLocal<List2Excel>();

	/**
	 * 创建excel返回excel的路径名
	 * @param request
	 * @param sheetName
	 * @param fileName
	 * @param lst
	 * @param starttime2
	 * @param endtime2
	 * @return
	 */
	public static List2Excel getInstance() {
		List2Excel eop = threadLocal.get();
		if (eop == null) {
			eop = new List2Excel();
			threadLocal.set(eop);
		}
		return eop;
	}

	public void removeInstance() {

		try {
			List2Excel eop = threadLocal.get();
			if (eop != null) {
				threadLocal.remove();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public String createExcel(HttpServletRequest request, String sheetName,
			List<Map<String, Object>> list,
			String[] title, String[] lable,
			Map<String, Map<String, String>> replaceMap) {
		
	       String path = null;
	   	WritableWorkbook book = null;
		try {
			//导出的临时目录
			path = (request.getSession().getServletContext()
					.getRealPath("/"))
					+ "ExcelTemp/"
					+ UUID.randomUUID().toString() + ".xls";
			if(null!=path)
			{
				path = path.replace("\\", "/");
			}
		
			book = Workbook.createWorkbook(new File(path));
			WritableSheet sheet = book.createSheet(sheetName, 0);
			sheet.getSettings().setDefaultColumnWidth(15);
			sheet.getSettings().setDefaultRowHeight(400);
			WritableFont font = new WritableFont(WritableFont.ARIAL, 14,
					WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf = new WritableCellFormat(font);
			int titlecount = 0;
			for(int i=0;i<title.length;i++)
			{
				Label label0 = new Label(titlecount, 0, title[i], cf);
				sheet.addCell(label0);
				titlecount++;
			}
			if(null!=list && !list.isEmpty())
			{
				for (int i = 0; i < list.size(); i++) {
					Map<String,Object> map = list.get(i);
					int count = 0;
					for (int j=0;j<lable.length;j++) {
						
						String value = map.get(lable[j]) == null ? "" : String.valueOf(map.get(lable[j]));
						
						//判断是否有字典
						if(null!=replaceMap)
						{
							if(null!=replaceMap.get(lable[j]))
							{
							   value = replaceMap.get(lable[j]).get(value);
							}
						}
						
						
						//添加超链接的
						/*if("AJBH".equals(lable[j]))
						{
							WritableHyperlink link= new WritableHyperlink(count,i+1,new URL("http://10.35.246.168:8080/dzda/ztk/saxwtz/saxwtz_detail.xhtml?ajbh="+value));
							link.setDescription(value);
							sheet.addHyperlink(link);
						}else if("GLQKLJ".equals(lable[j]))
						{
							
							WritableHyperlink link= new WritableHyperlink(count,i+1,new URL("http://10.35.246.168:8080/dzda/ztk/saxwtz/person_ry_list.xhtml?id="+map.get("ID")+"&sfzh="+map.get("SFZH")));
							link.setDescription("点击查看前科劣迹,关联涉案信息");
							sheet.addHyperlink(link);
						}
						else
						{
							Label _label = new Label(count, i+1, value);
							sheet.addCell(_label);
						}*/
						Label _label = new Label(count, i+1, value);
						sheet.addCell(_label);
						count++;
					}
				
				}
			}
			book.write();
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return path;
	}

	/**
	 * 导出excel
	 * 
	 * @param request
	 *            request 对象 不可为空
	 * @param response
	 *            对象 不可为空
	 * @param list
	 *            要导出的 list队列 存放的是HashMap<String,Object>
	 * @param excelName
	 *            要导出excel文件的名字 不可为空;
	 * @param titleMap
	 *            标题Map 最好 不可为空;
	 * @param lableList
	 *            list队列 存放的是HashMap<String,Object> 中Key 的List 最好不为空
	 * @param replaceMap
	 *            要替换的 值的map 比如记录 中值 1代表男 2代表女 可为空      可以将  1替换成男
	 */
	public void ExportExcel(HttpServletRequest request,
			HttpServletResponse response, List<Map<String, Object>> list,
			String excelName, String[] title,
			String[] lable,
			Map<String, Map<String, String>> replaceMap) {
	
		File tempFile = null;
		String filePath = null;
		try {
			filePath = List2Excel.getInstance().createExcel(request,
					excelName, list, title, lable, replaceMap);
			File  file = new File(filePath);
			List2Excel.getInstance().downloadFile(response,
					excelName + ".xls", file);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			tempFile = new File(filePath);
			if (tempFile != null && tempFile.isFile()) {
				tempFile.delete();
			}
		}
		
	}

	
	
	public String sqlcreateExcel(HttpServletRequest request, String sheetName,
			List<Map<String,Object>> lst,
			HashMap<String, String> titleMap, List<String> lableList,
			HashMap<String, HashMap<String, String>> replaceMap) {
		
		try {
			String path = (request.getSession().getServletContext()
					.getRealPath("/"))
					+ "/ExcelTemp/"
					+ UUID.randomUUID().toString() + ".xls";
			if(null!=path)
			{
				path = path.replace("\\", "/");
			}
			WritableWorkbook book = null;
			book = Workbook.createWorkbook(new File(path));
			WritableSheet sheet = book.createSheet(sheetName, 0);
			sheet.getSettings().setDefaultColumnWidth(15);
			sheet.getSettings().setDefaultRowHeight(400);
		    WritableFont font = new WritableFont(WritableFont.ARIAL, 14,
			WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf = new WritableCellFormat(font);
	        int titlecount = 0;
			for (String s : lableList) {
				Label label0 = new Label(titlecount, 0, titleMap.get(s), cf);
				sheet.addCell(label0);
				titlecount++;
			}
		
			for (int i = 0; i < lst.size() && lst.size() > 0; i++) {
				Map map = lst.get(i);
				int count = 0;
				for (String key : lableList) {
					String value = map.get(key) == null ? "" : map.get(key)
							.toString();
					Label _label = new Label(count, i+1, value);
					sheet.addCell(_label);
					count++;
				}
			}
			
			book.write();
			book.close();
			return path;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	/**
	 * 导出excel
	 * 
	 * @param request
	 *            request 对象 不可为空
	 * @param response
	 *            对象 不可为空
	 * @param list
	 *            要导出的 list队列 存放的是HashMap<String,Object>
	 * @param excelName
	 *            要导出excel文件的名字 不可为空;
	 * @param titleMap
	 *            标题Map 最好 不可为空;
	 * @param lableList
	 *            list队列 存放的是HashMap<String,Object> 中Key 的List 最好不为空
	 * @param replaceMap
	 *            要替换的 值的map 比如记录 中值 1代表男 2代表女 可为空      可以将  1替换成男
	 */
	public void sqlExportExcel(HttpServletRequest request,
			HttpServletResponse response, List<Map<String, Object>> list,
			String excelName, HashMap<String, String> titleMap,
			List<String> lableList,
			HashMap<String, HashMap<String, String>> replaceMap) {
		File tempFile = null;
		String filePath = null;
		try {
			filePath = List2Excel.getInstance().sqlcreateExcel(request,
					excelName, list, titleMap, lableList, replaceMap);
			File file = new File(filePath);
			List2Excel.getInstance().downloadFile(response,
					excelName + ".xls", file);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			tempFile = new File(filePath);
			if (tempFile != null && tempFile.isFile()) {
				tempFile.delete();
			}
		}
		
	}
	
	
	
	/**
	 * 文件下载
	 * 
	 * @param response
	 * @param fileName
	 * @param file
	 */
	public void downloadFile(HttpServletResponse response, String fileName,
			File file) {

		response.reset();
		InputStream is = null;
		OutputStream os = null;
		try {
			response.setContentType("application/octet-stream");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String((fileName).getBytes("GBK"), "ISO-8859-1"));
			is = new FileInputStream(file);
			os = new BufferedOutputStream(response.getOutputStream());
			byte[] bytes = new byte[4096];
			int len;
			while ((len = is.read(bytes)) != -1) {
				os.write(bytes, 0, len);
			}
		} catch (Exception ex) {

		} finally {
			try {
				os.flush();
				os.close();
				is.close();
			} catch (IOException e) {

			}
		}
	}
	

}

